#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

set hive.auto.convert.join=false;
set hive.exec.dynamic.partition.mode=nonstrict;
--分区
insert into table db_dwm.student_attendance_dwm partition(yearinfo,monthinfo,dayinfo)
select
    ctudd.class_date,
    ctudd.class_id,
    student_id,
    if(
        sum(
            if( 
                unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')
                    between unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60   
                        and  unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_end_time),'yyyy-MM-dd HH:mm:ss')
            ,1,0)
        
        ) > 0,  -- 如果大于0 认为当天的打卡记录中, 一定是有出勤的打卡记录, 如果小于等于0 认为没有出勤
       if(
           sum(
              if(
                 unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') 
                    between unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60  
                        and  unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60
                , 1 , 0)
           ) >0, -- 如果大于0, 认为当天打卡记录中, 一定是有正常出勤的记录, 否则认为迟到出勤
       0,1),
    2)as morning_att,

       if(
        sum(
            if(
                unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')
                    between unix_timestamp(concat(ctudd.class_date,' ',ctd. afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60
                        and  unix_timestamp(concat(ctudd.class_date,' ',ctd.afternoon_end_time),'yyyy-MM-dd HH:mm:ss')
            ,1,0)

        ) > 0,  -- 如果大于0 认为当天的打卡记录中, 一定是有出勤的打卡记录, 如果小于等于0 认为没有出勤
       if(
           sum(
              if(
                 unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')
                    between unix_timestamp(concat(ctudd.class_date,' ',ctd. afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60
                        and  unix_timestamp(concat(ctudd.class_date,' ',ctd.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60
                , 1 , 0)
           ) >0, -- 如果大于0, 认为当天打卡记录中, 一定是有正常出勤的记录, 否则认为迟到出勤
       0,1),
    2)as afternoon_att,

      if(
        sum(
            if(
                unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')
                    between unix_timestamp(concat(ctudd.class_date,ctd. evening_begin_time,' '),'yyyy-MM-dd HH:mm:ss') - 40*60
                        and  unix_timestamp(concat(ctudd.class_date,ctd.evening_end_time,' '),'yyyy-MM-dd HH:mm:ss')
            ,1,0)

        ) > 0,  -- 如果大于0 认为当天的打卡记录中, 一定是有出勤的打卡记录, 如果小于等于0 认为没有出勤
       if(
           sum(
              if(
                 unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')
                    between unix_timestamp(concat(ctudd.class_date,' ',ctd. evening_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60
                        and  unix_timestamp(concat(ctudd.class_date,' ',ctd.evening_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60
                , 1 , 0)
           ) >0, -- 如果大于0, 认为当天打卡记录中, 一定是有正常出勤的记录, 否则认为迟到出勤
       0,1),
    2) as  evening_att,
    substr(ctudd.class_date,1,4) as yearinfo,
    substr(ctudd.class_date,6,2) as monthinfo,
    substr(ctudd.class_date,9,2) as dayinfo
from (select * from db_ods.course_table_upload_detail where nvl(content,'')!='' and content !='开班典礼') ctudd
     left  join (select * from db_ods.tbh_student_signin_record where share_state = 1) sso on sso.class_id = ctudd.class_id
     left join db_ods.tbh_class_time_table ctd on ctd.id = sso.time_table_id
where ctudd.class_date in ('2019-09-03','2019-09-04','2019-09-05')
group  by ctudd.class_date , ctudd.class_id,sso.student_id;


select unix_timestamp('2021-10-08 15:40:30','yyyy-MM-dd HH:mm:ss') - 40*60; -- 1633676430

insert into  table db_dwm.class_attendance_dwm partition(yearinfo,monthinfo,dayinfo)
select
    dateinfo,
    class_id,
    count(
        case
            when morning_att in ('0','1') then student_id
            else null end
    ) as morning_att_count,


    count(
        case
            when afternoon_att in ('0','1') then student_id
            else null end
    ) as afternoon_att_count,

    count(
        case
            when evening_att in ('0','1') then student_id
            else null end
    ) as evening_att_count,

    sum(
        case
            when morning_att ='1' then 1
            else 0 end
    ) as morning_late_count,

    sum(
        case
            when afternoon_att ='1' then 1
            else 0 end
    ) as afternoon_late_count,

    sum(
        case
            when evening_att ='1' then 1
            else 0 end
    ) as evening_late_count,

    yearinfo,
    monthinfo,
    dayinfo
from db_dwm.student_attendance_dwm
group by  dateinfo,yearinfo,monthinfo,dayinfo,class_id;





with A as (select
    ctudd.class_date,
    ctudd.class_id,
    count(distinct sla.student_id) as morning_leave_count
from (select * from db_ods.student_leave_apply where audit_state = 1 and cancel_state = 0 and valid_state = 1) sla
    join db_ods.tbh_class_time_table ctd on sla.class_id = ctd.class_id
    join (select * from db_ods. course_table_upload_detail where nvl(content,'')!='' and content != '开班典礼') ctudd on ctudd.class_id = sla.class_id
where ctudd.class_date between ctd.use_begin_date  and  ctd.use_end_date 
    and concat(ctudd.class_date,' ',ctd.morning_begin_time) >= sla.begin_time
    and concat(ctudd.class_date,' ',ctd.morning_begin_time) <= sla.end_time
group by ctudd.class_date,ctudd.class_id),
	B AS (select
    ctudd.class_date,
    ctudd.class_id,
    count(distinct sla.student_id) as afternoon_leave_count
from (select * from db_ods.student_leave_apply where audit_state = 1 and cancel_state = 0 and valid_state = 1) sla
    join db_ods.tbh_class_time_table  ctd on sla.class_id = ctd.class_id
    join (select * from db_ods. course_table_upload_detail where nvl(content,'')!='' and content != '开班典礼') ctudd on ctudd.class_id = sla.class_id
where ctudd.class_date between ctd.use_begin_date  and  ctd.use_end_date 
    and concat(ctudd.class_date,' ',ctd.afternoon_begin_time) >= sla.begin_time
    and concat(ctudd.class_date,' ',ctd.afternoon_begin_time) <= sla.end_time
group by ctudd.class_date,ctudd.class_id),
	C AS (select
    ctudd.class_date,
    ctudd.class_id,
    count(distinct sla.student_id) as evening_leave_count
from (select * from db_ods.student_leave_apply where audit_state = 1 and cancel_state = 0 and valid_state = 1) sla
    join db_ods.tbh_class_time_table ctd on sla.class_id = ctd.class_id
    join (select * from db_ods. course_table_upload_detail where nvl(content,'')!='' and content != '开班典礼') ctudd on ctudd.class_id = sla.class_id
where ctudd.class_date between ctd.use_begin_date  and  ctd.use_end_date 
    and concat(ctudd.class_date,' ',ctd.evening_begin_time) >= sla.begin_time
    and concat(ctudd.class_date,' ',ctd.evening_begin_time) <= sla.end_time
group by ctudd.class_date,ctudd.class_id),
temp as (select  
	coalesce(A.class_date,B.class_date,C.class_date) AS class_date,
	coalesce(A.class_id,B.class_id,C.class_id) AS class_id,
	nvl(A.morning_leave_count,0) as morning_leave_count,
	nvl(B.afternoon_leave_count,0) as afternoon_leave_count,
	nvl(C.evening_leave_count,0) as evening_leave_count
from A 
	full join B on A.class_date = B.class_date and A.class_id = B.class_id
	full join C on A.class_date = C.class_date and A.class_id = C.class_id)
insert into table db_dwm.class_leave_dwm partition(yearinfo,monthinfo,dayinfo)
select 
	class_date,
	class_id,
	sum(morning_leave_count) as morning_leave_count,
	sum(afternoon_leave_count) as afternoon_leave_count,
	sum(evening_leave_count) as evening_leave_count,
	substr(class_date,1,4) as yearinfo,
	substr(class_date,6,2) as monthinfo,
	substr(class_date,9,2) as dayinfo
from temp group by class_date,class_id;

insert into table db_dwm.class_truant_dwm partition(yearinfo,monthinfo,dayinfo)
select
    ctudd.class_date as dateinfo,
    ctudd.class_id,
    cssc.studying_student_count - nvl(cad.morning_att_count,0) - nvl(cld.morning_leave_count,0) as morning_truant_count,
    cssc.studying_student_count - nvl(cad.afternoon_att_count,0) - nvl(cld.afternoon_leave_count,0) as afternoon_truant_count,
    cssc.studying_student_count - nvl(cad.evening_att_count,0) - nvl(cld.evening_leave_count,0) as evening_truant_count,
    substr(ctudd.class_date,1,4) as yearinfo,
    substr(ctudd.class_date,6,2) as monthinfo,
    substr(ctudd.class_date,9,2) as dayinfo
from (select * from db_ods.course_table_upload_detail where nvl(content,'')!='' and content != '开班典礼') ctudd
    left join db_ods.class_studying_student_count cssc on  ctudd.class_date = cssc.studying_date and ctudd.class_id = cssc.class_id
    left join db_dwm.class_attendance_dwm cad on ctudd.class_id = cad.class_id and ctudd.class_date = cad.dateinfo
    left join db_dwm.class_leave_dwm cld on ctudd.class_id = cld.class_id and ctudd.class_date = cld.dateinfo
where ctudd.class_date in('2019-09-03','2019-09-04','2019-09-05');


insert into table db_dwm.class_all_dwm partition(yearinfo,monthinfo,dayinfo)
select
    ctudd.class_date as dateinfo,
    ctudd.class_id,
    cssc.studying_student_count,
    cad.morning_att_count,
    concat(round(nvl(cad.morning_att_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_att_ratio,
    cad.afternoon_att_count,
    concat(round(nvl(cad.afternoon_att_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_att_ratio,
    cad.evening_att_count,
    concat(round(nvl(cad.evening_att_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_att_ratio,
    cad.morning_late_count,
    concat(round(nvl(cad.morning_late_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_late_ratio,
    cad.afternoon_late_count,
    concat(round(nvl(cad.afternoon_late_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_late_ratio,
    cad.evening_late_count,
    concat(round(nvl(cad.evening_late_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_late_ratio,
    cld.morning_leave_count,
    concat(round(nvl(cld.morning_leave_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_leave_ratio,
    cld.afternoon_leave_count,
    concat(round(nvl(cld.afternoon_leave_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_leave_ratio,
    cld.evening_leave_count,
    concat(round(nvl(cld.evening_leave_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_leave_ratio,
    ctd.morning_truant_count,
    concat(round(nvl(ctd.morning_truant_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_truant_ratio,
    ctd.afternoon_truant_count,
    concat(round(nvl(ctd.afternoon_truant_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_truant_ratio,
    ctd.evening_truant_count,
    concat(round(nvl(ctd.evening_truant_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_truant_ratio,
    substr(ctudd.class_date,1,4) as yearinfo,
    substr(ctudd.class_date,6,2) as monthinfo,
    substr(ctudd.class_date,9,2) as dayinfo
from (select * from db_ods.course_table_upload_detail where nvl(content,'')!='' and content != '开班典礼') ctudd
    left join db_ods.class_studying_student_count cssc on cssc.class_id = ctudd.class_id and cssc.studying_date = ctudd.class_date
    left join db_dwm.class_attendance_dwm cad on ctudd.class_id = cad.class_id and ctudd.class_date = cad.dateinfo
    left join db_dwm.class_leave_dwm cld on ctudd.class_id = cld.class_id and ctudd.class_date = cld.dateinfo
    left join db_dwm.class_truant_dwm ctd on ctudd.class_id = ctd.class_id and ctudd.class_date = ctd.dateinfo
where ctudd.class_date in ('2019-09-03','2019-09-04','2019-09-05');

insert into table db_dws.class_attendance_dws partition(yearinfo,monthinfo,dayinfo)
select
    concat(yearinfo,'-',monthinfo) as dateinfo,
    class_id,
    sum(studying_student_count) as studying_student_count,

    sum(morning_att_count) as  morning_att_count,
    concat(round(sum(morning_att_count) / sum(studying_student_count) *100,2),'%') as morning_att_ratio,
    sum(afternoon_att_count) as  afternoon_att_count,
    concat(round(sum(afternoon_att_count) / sum(studying_student_count) *100,2),'%') as afternoon_att_ratio,
    sum(evening_att_count) as evening_att_count,
    concat(round(sum(evening_att_count) / sum(studying_student_count) *100,2),'%') as evening_att_ratio,

    sum(morning_late_count) as  morning_late_count,
    concat(round(sum(morning_late_count) / sum(studying_student_count) *100,2),'%') as morning_late_ratio,
    sum(afternoon_late_count) as  afternoon_late_count,
    concat(round(sum(afternoon_late_count) / sum(studying_student_count) *100,2),'%') as afternoon_late_ratio,
    sum(evening_late_count) as evening_late_count,
    concat(round(sum(evening_late_count) / sum(studying_student_count) *100,2),'%') as evening_late_ratio,

    sum(morning_leave_count) as  morning_leave_count,
    concat(round(sum(morning_leave_count) / sum(studying_student_count) *100,2),'%') as morning_leave_ratio,
    sum(afternoon_leave_count) as  afternoon_leave_count,
    concat(round(sum(afternoon_leave_count) / sum(studying_student_count) *100,2),'%') as afternoon_leave_ratio,
    sum(evening_leave_count) as evening_leave_count,
    concat(round(sum(evening_leave_count) / sum(studying_student_count) *100,2),'%') as evening_leave_ratio,

    sum(morning_truant_count) as  morning_truant_count,
    concat(round(sum(morning_truant_count) / sum(studying_student_count) *100,2),'%') as morning_truant_ratio,
    sum(afternoon_truant_count) as  afternoon_truant_count,
    concat(round(sum(afternoon_truant_count) / sum(studying_student_count) *100,2),'%') as afternoon_truant_ratio,
    sum(evening_truant_count) as evening_truant_count,
    concat(round(sum(evening_truant_count) / sum(studying_student_count) *100,2),'%') as evening_truant_ratio,

    '4' as  time_type,
    yearinfo,
    monthinfo,
    '-1' as dayinfo

from  db_dwm.class_all_dwm
group by yearinfo,monthinfo,class_id;

"


